﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;

namespace DotNETX.Utility
{
    public class ExcelUtils
    {
        public static string[] ReadSheetNames(string excelFile)
        {
            try
            {
                if (System.IO.File.Exists(excelFile))
                {
                    OleDbConnection con_excel = new OleDbConnection(GenerateConnectionString(excelFile, false));
                    con_excel.Open();
                    DataTable table = con_excel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    con_excel.Close();
                    string[] sheets = new string[table.Rows.Count];
                    int i = 0;
                    foreach (DataRow row in table.Rows)
                    {
                        sheets[i] = row["Table_Name"].ToString();
                        i++;
                    }
                    return sheets;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }

        public static DataTable ReadExcel(string excelFile, string sheetName)
        {
            try
            {
                if (System.IO.File.Exists(excelFile))
                {
                    OleDbConnection con_excel = new OleDbConnection(GenerateConnectionString(excelFile, true));
                    con_excel.Open();
                    OleDbCommand cmd = con_excel.CreateCommand();
                    cmd.CommandText = string.Format("Select * From [{0}$]", sheetName);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    DataTable table = ds.Tables[0];
                    return table;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }

        public static void SaveExcel(DataTable dt, string filter, string outputExcelFile, string sheetName)
        {
            try
            {
                System.IO.File.Delete(outputExcelFile);
            }
            catch
            {
                return;
            }

            string ConnStr;
            ConnStr = GenerateConnectionString(outputExcelFile);

            OleDbConnection conn_excel = new OleDbConnection();
            conn_excel.ConnectionString = ConnStr;

            OleDbCommand cmd_excel = new OleDbCommand();

            string sql;
            sql = SqlCreateSheet(dt, sheetName);

            conn_excel.Open();
            cmd_excel.Connection = conn_excel;
            cmd_excel.CommandText = sql;
            cmd_excel.ExecuteNonQuery();

            conn_excel.Close();

            OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + sheetName + "$]", conn_excel);
            DataTable dt_excel = new DataTable();
            da_excel.Fill(dt_excel);

            da_excel.InsertCommand = SqlCreateInsertCommand(sheetName, dt, conn_excel);

            DataRow dr_excel;
            string ColumnName;

            foreach (DataRow dr in dt.Select(filter))
            {
                dr_excel = dt_excel.NewRow();

                foreach (DataColumn dc in dt.Columns)
                {
                    ColumnName = dc.ColumnName;
                    dr_excel[ColumnName] = dr[ColumnName];

                }
                dt_excel.Rows.Add(dr_excel);

            }

            da_excel.Update(dt_excel);
            conn_excel.Close();
        }

        public static string GenerateConnectionString(string excelFile, bool tryConnect = false)
        {
            FileInfo fi = new FileInfo(excelFile);
            string connString = null;
            if (!string.IsNullOrEmpty(fi.Extension))
            {
                if (fi.Extension.ToLower() == ".xlsx")
                {
                    connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{0}\";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", excelFile);
                    if (tryConnect)
                    {
                        if (TryConnect(connString)) return connString;
                    }
                    else
                    {
                        return connString;
                    }
                }
            }
            connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=YES\";", excelFile);
            if (tryConnect)
            {
                if (TryConnect(connString)) return connString;
            }
            else
            {
                return connString;
            }
            return null;
        }

        private static bool TryConnect(string connString)
        {

            OleDbConnection con_excel = new OleDbConnection(connString);
            try
            {
                con_excel.Open();
                con_excel.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        private static string GetDataType(Type i)
        {
            string s;

            switch (i.Name)
            {
                case "String":
                    s = "Char";
                    break;
                case "Int32":
                    s = "Int";
                    break;
                case "Int64":
                    s = "Int";
                    break;
                case "Int16":
                    s = "Int";
                    break;
                case "Double":
                    s = "Double";
                    break;
                case "Decimal":
                    s = "Double";
                    break;
                default:
                    s = "Char";
                    break;

            }
            return s;
        }

        private static OleDbType StringToOleDbType(Type i)
        {
            OleDbType s;

            switch (i.Name)
            {
                case "String":
                    s = OleDbType.Char;
                    break;
                case "Int32":
                    s = OleDbType.Integer;
                    break;
                case "Int64":
                    s = OleDbType.Integer;
                    break;
                case "Int16":
                    s = OleDbType.Integer;
                    break;
                case "Double":
                    s = OleDbType.Double;
                    break;
                case "Decimal":
                    s = OleDbType.Decimal;
                    break;
                default:
                    s = OleDbType.Char;
                    break;

            }
            return s;

        }

        private static string SqlCreateSheet(DataTable dt, string SheetName)
        {
            string sql;

            sql = "CREATE TABLE [" + SheetName + "] (";

            foreach (DataColumn dc in dt.Columns)
            {
                sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";
            }

            //sql = "CREATE TABLE [" + SheetName + "] (";

            //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)
            //{
            //    sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";
            //}
            //sql = sql.Substring(0, sql.Length - 1);
            //sql += ")";

            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";

            return sql;
        }

        // 生成 InsertCommand 并设置参数
        private static OleDbCommand SqlCreateInsertCommand(string SheetName, DataTable dt, OleDbConnection conn_excel)
        {
            OleDbCommand i;
            string sql;

            sql = "INSERT INTO [" + SheetName + "$] (";
            foreach (DataColumn dc in dt.Columns)
            {
                sql += "[" + dc.ColumnName + "] ";
                sql += ",";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ") VALUES (";
            foreach (DataColumn dc in dt.Columns)
            {
                sql += "?,";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";

            i = new OleDbCommand(sql, conn_excel);

            foreach (DataColumn dc in dt.Columns)
            {
                i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);
            }

            return i;
        }
    }
}
